#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Excel数据处理脚本 - 将sbb.xlsx转换为多语言数据库SQL
处理顺序：D列(分类) -> B列+C列(症状) -> A列+B列+C列(汤药)
"""

import pandas as pd
import re
import time
import requests
import json
from typing import Dict, List, Set, Tuple
from urllib.parse import quote


class MultiLanguageTranslator:
    """多语言翻译器"""

    def __init__(self):
        self.cache = {}  # 翻译缓存，避免重复翻译

    def translate_text(self, text: str, target_lang: str) -> str:
        """
        翻译文本
        target_lang: 'en' (英语), 'ja' (日语), 'be' (白俄罗斯语)
        """
        if not text or text.strip() == "":
            return ""

        cache_key = f"{text}_{target_lang}"
        if cache_key in self.cache:
            return self.cache[cache_key]

        try:
            # 使用免费的翻译API（这里用模拟翻译，实际使用时需要替换为真实API）
            translated = self._mock_translate(text, target_lang)
            self.cache[cache_key] = translated
            time.sleep(0.1)  # 避免频率限制
            return translated
        except Exception as e:
            print(f"翻译失败: {text} -> {target_lang}, 错误: {str(e)}")
            return text  # 翻译失败时返回原文

    def _mock_translate(self, text: str, target_lang: str) -> str:
        """模拟翻译（实际使用时需要替换为真实翻译API）"""
        # 这里使用简单的模拟翻译，实际应该调用Google Translate或百度翻译API
        lang_maps = {
            'en': {
                '头颈部症状': 'Head & Neck Symptoms',
                '呼吸系统症状': 'Respiratory Symptoms',
                '消化系统症状': 'Digestive Symptoms',
                '循环系统症状': 'Circulatory Symptoms',
                '其他症状': 'Other Symptoms',
                '头痛': 'Headache',
                '头晕': 'Dizziness',
                '咳嗽': 'Cough',
                '恶心': 'Nausea',
                '桂枝汤': 'Cinnamon Twig Decoction'
            },
            'ja': {
                '头颈部症状': '頭頸部症状',
                '呼吸系统症状': '呼吸器症状',
                '消化系统症状': '消化器症状',
                '循环系统症状': '循環器症状',
                '其他症状': 'その他の症状',
                '头痛': '頭痛',
                '头晕': 'めまい',
                '咳嗽': '咳',
                '恶心': '吐き気',
                '桂枝汤': '桂枝湯'
            },
            'be': {
                '头颈部症状': 'Сімптомы галавы і шыі',
                '呼吸系统症状': 'Сімптомы дыхальнай сістэмы',
                '消化系统症状': 'Сімптомы стрававальнай сістэмы',
                '循环系统症状': 'Сімптомы кровазвярнення',
                '其他症状': 'Іншыя сімптомы',
                '头痛': 'Галаўны боль',
                '头晕': 'Запаморкі',
                '咳嗽': 'Кашаль',
                '恶心': 'Млосць',
                '桂枝汤': 'Адвар корніца карыцы'
            }
        }

        if target_lang in lang_maps and text in lang_maps[target_lang]:
            return lang_maps[target_lang][text]

        # 如果没有预设翻译，返回带语言标识的原文（实际使用时需要调用真实API）
        lang_prefix = {'en': 'EN:', 'ja': 'JA:', 'be': 'BE:'}
        return f"{lang_prefix.get(target_lang, '')}{text}"


class ExcelToSqlProcessor:
    """Excel到SQL处理器"""

    def __init__(self, excel_path: str):
        self.excel_path = excel_path
        self.translator = MultiLanguageTranslator()
        self.categories = {}  # 分类映射 {中文名: id}
        self.symptoms = {}    # 症状映射 {中文名: id}
        self.sql_statements = []  # 存储所有SQL语句

    def clean_text(self, text: str) -> str:
        """清理文本：去除空格，统一逗号"""
        if pd.isna(text) or text is None:
            return ""

        text = str(text).strip()
        # 替换中文逗号为英文逗号，去除所有空格
        text = re.sub(r'[，,]\s*', ',', text)
        text = re.sub(r'\s+', '', text)
        return text

    def split_by_comma(self, text: str) -> List[str]:
        """按逗号分割并去重"""
        if not text:
            return []

        items = [item.strip() for item in text.split(',') if item.strip()]
        return list(set(items))  # 去重

    def read_excel_data(self) -> pd.DataFrame:
        """读取Excel数据"""
        try:
            df = pd.read_excel(self.excel_path)
            print(f"成功读取Excel文件，共 {len(df)} 行数据")

            # 重命名列名以便处理
            df.columns = ['汤药名称', '主证状', '兼症状', '所属分类']

            # 清理所有文本数据
            for col in df.columns:
                df[col] = df[col].apply(self.clean_text)

            return df
        except Exception as e:
            raise Exception(f"读取Excel文件失败: {str(e)}")

    def process_categories(self, df: pd.DataFrame) -> None:
        """处理D列分类数据"""
        print("开始处理分类数据...")

        # 收集所有分类
        all_categories = set()
        for categories_str in df['所属分类']:
            categories = self.split_by_comma(categories_str)
            all_categories.update(categories)

        # 移除空字符串
        all_categories = {cat for cat in all_categories if cat}

        # 添加"其他症状"分类作为默认分类
        all_categories.add("其他症状")

        print(f"发现 {len(all_categories)} 个分类: {list(all_categories)}")

        # 生成分类SQL
        self.sql_statements.append("-- ==========================================")
        self.sql_statements.append("-- 症状分类表数据")
        self.sql_statements.append("-- ==========================================")

        category_id = 1
        for category in sorted(all_categories):
            if not category:
                continue

            # 翻译成其他语言
            name_en = self.translator.translate_text(category, 'en')
            name_ja = self.translator.translate_text(category, 'ja')
            name_be = self.translator.translate_text(category, 'be')

            # 生成INSERT语句
            sql = f"""INSERT INTO `ai_symptom_categories` (
    `name_zh_cn`, `name_en_us`, `name_ja_jp`, `name_be_by`,
    `description_zh_cn`, `description_en_us`, `description_ja_jp`, `description_be_by`,
    `sort_order`, `is_active`
) VALUES (
    '{category}', '{name_en}', '{name_ja}', '{name_be}',
    '{category}相关症状', '{name_en} related symptoms', '{name_ja}関連症状', '{name_be} звязаныя сімптомы',
    {category_id}, TRUE
);"""

            self.sql_statements.append(sql)
            self.categories[category] = category_id
            category_id += 1

        print(f"生成了 {len(self.categories)} 个分类的SQL语句")

    def find_category_for_symptom(self, symptom: str, row_categories: List[str]) -> int:
        """为症状找到对应的分类ID"""
        if not row_categories:
            return self.categories.get("其他症状", 1)

        # 优先使用第一个分类
        for category in row_categories:
            if category in self.categories:
                return self.categories[category]

        # 如果都没找到，返回"其他症状"
        return self.categories.get("其他症状", 1)

    def process_symptoms(self, df: pd.DataFrame) -> None:
        """处理B列和C列症状数据"""
        print("开始处理症状数据...")

        # 收集所有症状及其对应的分类
        symptom_category_map = {}  # {症状: [分类列表]}

        for index, row in df.iterrows():
            row_categories = self.split_by_comma(row['所属分类'])

            # 处理主证状
            main_symptoms = self.split_by_comma(row['主证状'])
            for symptom in main_symptoms:
                if symptom and symptom not in symptom_category_map:
                    symptom_category_map[symptom] = row_categories

            # 处理兼症状
            side_symptoms = self.split_by_comma(row['兼症状'])
            for symptom in side_symptoms:
                if symptom and symptom not in symptom_category_map:
                    symptom_category_map[symptom] = row_categories

        print(f"发现 {len(symptom_category_map)} 个症状")

        # 生成症状SQL
        self.sql_statements.append("\n-- ==========================================")
        self.sql_statements.append("-- 症状表数据")
        self.sql_statements.append("-- ==========================================")

        symptom_id = 1
        for symptom, categories in sorted(symptom_category_map.items()):
            if not symptom:
                continue

            # 找到对应的分类ID
            category_id = self.find_category_for_symptom(symptom, categories)

            # 翻译成其他语言
            name_en = self.translator.translate_text(symptom, 'en')
            name_ja = self.translator.translate_text(symptom, 'ja')
            name_be = self.translator.translate_text(symptom, 'be')

            # 生成INSERT语句
            sql = f"""INSERT INTO `ai_symptoms` (
    `category_id`, `name_zh_cn`, `name_en_us`, `name_ja_jp`, `name_be_by`,
    `description_zh_cn`, `description_en_us`, `description_ja_jp`, `description_be_by`,
    `keywords_zh_cn`, `keywords_en_us`, `keywords_ja_jp`, `keywords_be_by`,
    `sort_order`, `is_active`
) VALUES (
    {category_id}, '{symptom}', '{name_en}', '{name_ja}', '{name_be}',
    '{symptom}的症状描述', '{name_en} symptom description', '{name_ja}の症状説明', '{name_be} апісанне сімптому',
    '{symptom}', '{name_en}', '{name_ja}', '{name_be}',
    {symptom_id}, TRUE
);"""

            self.sql_statements.append(sql)
            self.symptoms[symptom] = symptom_id
            symptom_id += 1

        print(f"生成了 {len(self.symptoms)} 个症状的SQL语句")

    def process_formulas(self, df: pd.DataFrame) -> None:
        """处理A列汤药数据"""
        print("开始处理汤药数据...")

        self.sql_statements.append("\n-- ==========================================")
        self.sql_statements.append("-- 汤药表数据")
        self.sql_statements.append("-- ==========================================")

        formula_id = 1
        for index, row in df.iterrows():
            formula_name = row['汤药名称']
            if not formula_name:
                continue

            # 获取主证状和兼症状ID
            main_symptoms = self.split_by_comma(row['主证状'])
            side_symptoms = self.split_by_comma(row['兼症状'])

            main_symptom_ids = [str(self.symptoms[s]) for s in main_symptoms if s in self.symptoms]
            side_symptom_ids = [str(self.symptoms[s]) for s in side_symptoms if s in self.symptoms]

            # 翻译汤药名称
            name_en = self.translator.translate_text(formula_name, 'en')
            name_ja = self.translator.translate_text(formula_name, 'ja')
            name_be = self.translator.translate_text(formula_name, 'be')

            # 生成INSERT语句
            sql = f"""INSERT INTO `ai_formulas` (
    `name_zh_cn`, `name_en_us`, `name_ja_jp`, `name_be_by`,
    `description_zh_cn`, `description_en_us`, `description_ja_jp`, `description_be_by`,
    `ingredients_zh_cn`, `ingredients_en_us`, `ingredients_ja_jp`, `ingredients_be_by`,
    `usage_method_zh_cn`, `usage_method_en_us`, `usage_method_ja_jp`, `usage_method_be_by`,
    `effects_zh_cn`, `effects_en_us`, `effects_ja_jp`, `effects_be_by`,
    `main_symptom_ids`, `side_symptom_ids`,
    `sort_order`, `is_active`
) VALUES (
    '{formula_name}', '{name_en}', '{name_ja}', '{name_be}',
    '{formula_name}的中医汤药', '{name_en} Traditional Chinese Medicine', '{name_ja}の中医学薬', '{name_be} традыцыйная кітайская медыцына',
    '具体配方待补充', 'Specific formula to be added', '具体的な処方は後で追加', 'Канкрэтная формула будзе дададзена',
    '煎服方法待补充', 'Decoction method to be added', '煎じ方は後で追加', 'Метад прыгатавання будзе дададзены',
    '功效作用待补充', 'Effects to be added', '効果は後で追加', 'Эфект будзе дададзены',
    '{",".join(main_symptom_ids)}', '{",".join(side_symptom_ids)}',
    {formula_id}, TRUE
);"""

            self.sql_statements.append(sql)
            formula_id += 1

        print(f"生成了 {formula_id - 1} 个汤药的SQL语句")

    def generate_sql_file(self, output_path: str) -> None:
        """生成最终的SQL文件"""
        try:
            with open(output_path, 'w', encoding='utf-8') as f:
                f.write("-- ==========================================\n")
                f.write("-- Excel数据导入SQL文件\n")
                f.write(f"-- 生成时间: {time.strftime('%Y-%m-%d %H:%M:%S')}\n")
                f.write("-- 数据来源: sbb.xlsx\n")
                f.write("-- ==========================================\n\n")

                f.write("SET NAMES utf8mb4;\n")
                f.write("SET FOREIGN_KEY_CHECKS = 0;\n\n")

                for sql in self.sql_statements:
                    f.write(sql + "\n\n")

                f.write("SET FOREIGN_KEY_CHECKS = 1;\n")
                f.write("-- SQL文件生成完成\n")

            print(f"SQL文件已生成: {output_path}")
        except Exception as e:
            raise Exception(f"生成SQL文件失败: {str(e)}")

    def process(self) -> str:
        """主处理流程"""
        try:
            # 1. 读取Excel数据
            df = self.read_excel_data()

            # 2. 处理分类（D列）
            self.process_categories(df)

            # 3. 处理症状（B列和C列）
            self.process_symptoms(df)

            # 4. 处理汤药（A列+B列+C列）
            self.process_formulas(df)

            # 5. 生成SQL文件
            output_path = self.excel_path.replace('.xlsx', '_processed.sql')
            self.generate_sql_file(output_path)

            return output_path

        except Exception as e:
            raise Exception(f"处理失败: {str(e)}")


def main():
    """主函数"""
    excel_path = r"F:\phpstudy_pro\intvale\ai_1\doc\sbb.xlsx"

    try:
        processor = ExcelToSqlProcessor(excel_path)
        output_file = processor.process()
        print(f"\n处理完成！")
        print(f"输出文件: {output_file}")
        print(f"分类数量: {len(processor.categories)}")
        print(f"症状数量: {len(processor.symptoms)}")

    except Exception as e:
        print(f"处理失败: {str(e)}")


if __name__ == "__main__":
    main()